/**
 * 
 */
package com.shine.icis.dao.imp;

import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;

import com.shine.cotrun.sql.hibernate.CommQuery;
import com.shine.cotrun.web.comm.ListPage;
import com.shine.icis.dao.IETLLogDAO;
import com.shine.icis.dao.imp.BaseHibernateDAO;
import com.shine.icis.dao.param.ETLRunLogQueryParameters;
import com.shine.icis.dto.DictOption;
import com.shine.icis.dto.ReturnMsg;
import com.shine.icis.hbean.ProgExeLog;
import com.shine.icis.hbean.ETLRunLog;

/**
 * @author zsy
 *
 */
public class ETLLogDAO extends BaseHibernateDAO implements IETLLogDAO {
	
	@SuppressWarnings("deprecation")
	@Override
	public ReturnMsg callProc(String proSql, int date) {
		try {
			CallableStatement proc = getSession().connection().prepareCall(proSql);
			proc.setInt(1, 0);
			proc.setInt(2, date);
			proc.setInt(3, date);
			proc.setString(4, "1");
			proc.setInt(5, 0);
			proc.setInt(6, 0);
			proc.registerOutParameter(7, Types.VARCHAR);
			proc.registerOutParameter(8, Types.INTEGER);
			
			proc.execute();
		    String msg = proc.getString(7);
		    int code = proc.getInt(8);
		    proc.close();
		    return new ReturnMsg(code, msg);
		} catch (SQLException e) {
			e.printStackTrace();
			throw new HibernateException(e);
		}
		
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<DictOption> findEtlProcOptions() {
		Query query = getSession().getNamedQuery("ETLLogDAO.findEtlProcOptions");
		List<Object[]> val = query.list();
		List<DictOption> ops = new ArrayList<DictOption>(val.size());
		for (Object[] oArr : val) {
			DictOption op = new DictOption();
			op.setKey(String.valueOf(oArr[0]));
			op.setText(String.valueOf(oArr[1]));
			ops.add(op);
		}
		return ops;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<DictOption> findPrepLevelOptions() {
		Query query = getSession().getNamedQuery("ETLLogDAO.findPrepLevelOptions");
		List<Object[]> val = query.list();
		List<DictOption> ops = new ArrayList<DictOption>(val.size());
		for (Object[] oArr : val) {
			DictOption op = new DictOption();
			op.setKey(String.valueOf(oArr[0]));
			op.setText(String.valueOf(oArr[1]));
			ops.add(op);
		}
		return ops;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<DictOption> findBizSystemOptions() {
		Query query = getSession().getNamedQuery("ETLLogDAO.findBizSystemOptions");
		List<Object[]> val = query.list();
		List<DictOption> ops = new ArrayList<DictOption>(val.size());
		for (Object[] oArr : val) {
			DictOption op = new DictOption();
			op.setKey(String.valueOf(oArr[0]));
			op.setText(String.valueOf(oArr[1]));
			ops.add(op);
		}
		return ops;
	}
	
//	@Override
//	public ListPage<ProgExeLog> findETLLogPage(ETLRunLogQueryParameters qp) {
//		if (qp == null) {
//			throw new IllegalArgumentException("非法参数:查询参数对象为null");
//		}
//		try {
//			StringBuffer sbQueryString = new StringBuffer();			
//			sbQueryString.append("select l from ETLLog as l where 1=1");
//			if(qp.getOperDate() != null){
//				sbQueryString.append(" and l.operDate=:operDate");
//			}
//			if(qp.getEtlProcID() != null){
//				sbQueryString.append(" and l.etlProcID=:etlProcID");
//			}
//			if(qp.getPrepLevelID() != null){
//				sbQueryString.append(" and l.prepLevelID=:prepLevelID");
//			}
//			
//			return new CommQuery<ProgExeLog>().queryListPage(qp, 
//					qp.appendOrders(sbQueryString, "l" ), getSession());
//		} catch(RuntimeException re) {
//			re.printStackTrace();
//			return new  ListPage<ProgExeLog>();
//		}
//	}
	
	@Override
	public ListPage<ETLRunLog> findETLRunLogPage(ETLRunLogQueryParameters qp) {
		if (qp == null) {
			throw new IllegalArgumentException("非法参数:查询参数对象为null");
		}
		try {
			StringBuffer sbQueryString = new StringBuffer();			
			sbQueryString.append("select l from ETLRunLog as l where 1=1");
			if(qp.getOperDate() != null){
				sbQueryString.append(" and l.operDate=:operDate");
			}
			if(qp.getEtlProcID() != null){
				sbQueryString.append(" and l.etlProcID=:etlProcID");
			}
			if(qp.getPrepLevelID() != null){
				sbQueryString.append(" and l.prepLevelID=:prepLevelID");
			}
			if(qp.getRunStatus() != null){
				sbQueryString.append(" and l.runStatus=:runStatus");
			}
			if(qp.getDatasetTypeID() != null){
				sbQueryString.append(" and l.datasetTypeID=:datasetTypeID");
			}
			if(qp.getStartLogID() != null){
				ProgExeLog srartLog = (ProgExeLog)getSession().load(ProgExeLog.class, qp.getStartLogID());
				qp.removeParameter("startLogID");
				qp.addParameter("operDate", srartLog.getOperDate());
				qp.addParameter("operTime", srartLog.getOperTime());
				sbQueryString.append(" and (l.operDate >:operDate or l.operDate =:operDate and l.operTime >=:operTime)");
			}
			
			return new CommQuery<ETLRunLog>().queryListPage(qp, 
					qp.appendOrders(sbQueryString, "l" ), getSession());
		} catch(RuntimeException re) {
			re.printStackTrace();
			return new  ListPage<ETLRunLog>();
		}
	}

	@Override
	public ProgExeLog findLastStartProcess() {
		Query query = getSession().createQuery(
				"select l from ProgExeLog as l where l.etlType=:etlType " +
				"and l.runStatus=:runStatus order by l.operDate desc, l.operTime desc, l.logID desc");
		query.setCharacter("etlType", ProgExeLog.ETL_TYPE_PRO);
		query.setCharacter("runStatus", ETLRunLog.RUN_STATUS_START);
		query.setMaxResults(1);
		return (ProgExeLog)query.uniqueResult();
	}
	
	
	
	@Override
	public ProgExeLog findLastEndProcess(int operDate, int operTime) {
		Query query = getSession().createQuery(
				"select l from ProgExeLog as l where l.etlType=:etlType and l.runStatus=:runStatus " +
				"and (l.operDate >:operDate or l.operDate =:operDate and l.operTime >=:operTime) " +
				"order by l.operDate desc, l.operTime, l.logID");
		query.setCharacter("etlType", ProgExeLog.ETL_TYPE_PRO);
		query.setCharacter("runStatus", ETLRunLog.RUN_STATUS_SUCCESS);
		query.setInteger("operDate", operDate);
		query.setInteger("operTime", operTime);
		query.setMaxResults(1);
		return (ProgExeLog)query.uniqueResult();
	}
	
	@Override
	public ProgExeLog findLastEndProcess() {
		Query query = getSession().createQuery(
				"select l from ProgExeLog as l where l.etlType=:etlType " +
				"and l.runStatus=:runStatus order by l.operDate desc, l.operTime desc, l.logID desc");
		query.setCharacter("etlType", ProgExeLog.ETL_TYPE_PRO);
		query.setCharacter("runStatus", ETLRunLog.RUN_STATUS_SUCCESS);
		query.setMaxResults(1);
		return (ProgExeLog)query.uniqueResult();
	}
	
	@Override
	public ProgExeLog findLastStartProcess(int operDate, int operTime) {
		Query query = getSession().createQuery(
				"select l from ProgExeLog as l where l.etlType=:etlType and l.runStatus=:runStatus " +
				"and (l.operDate <:operDate or l.operDate =:operDate and l.operTime <=:operTime) " +
				"order by l.operDate desc, l.operTime desc, l.logID desc");
		query.setCharacter("etlType", ProgExeLog.ETL_TYPE_PRO);
		query.setCharacter("runStatus", ETLRunLog.RUN_STATUS_START);
		query.setInteger("operDate", operDate);
		query.setInteger("operTime", operTime);
		query.setMaxResults(1);
		return (ProgExeLog)query.uniqueResult();
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<ProgExeLog> findProgExeLogs(int datasetTypeID, int etlProcID,
			int operDate, int operTime) {
		String hql = "select l from ProgExeLog as l where l.datasetTypeID=:datasetTypeID " +
				"and l.etlProcID=:etlProcID and l.etlType=:etlType " +
				"and (l.operDate >:operDate or l.operDate =:operDate and l.operTime >=:operTime)";
		hql += " order by l.operDate,l.operTime, l.logID";
		Query query = getSession().createQuery(hql);
		query.setInteger("datasetTypeID", datasetTypeID);
		query.setInteger("etlProcID", etlProcID);
		query.setInteger("operDate", operDate);
		query.setInteger("operTime", operTime);
		query.setCharacter("etlType", ProgExeLog.ETL_TYPE_JOB);
		return query.list();
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<ProgExeLog> findProgExeLogs(int etlProcID, int operDate, int operTime) {
		String hql = "select l from ProgExeLog as l where l.etlProcID=:etlProcID and l.etlType=:etlType " +
				"and (l.operDate >:operDate or l.operDate =:operDate and l.operTime >=:operTime)";
		hql += " order by l.operDate,l.operTime, l.logID";
		Query query = getSession().createQuery(hql);
		query.setInteger("etlProcID", etlProcID);
		query.setInteger("operDate", operDate);
		query.setInteger("operTime", operTime);
		query.setCharacter("etlType", ProgExeLog.ETL_TYPE_JOB);
		return query.list();
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<Integer> findETLRunLogPrepLevelIDs(int datasetTypeID, int etlProcID, 
			int operDate, int operTime, int prepLevelID) {
		String hql = "select l.prepLevelID from ETLRunLog as l where l.datasetTypeID=:datasetTypeID " +
				"and l.etlProcID=:etlProcID and (l.operDate >:operDate or l.operDate =:operDate and l.operTime >=:operTime) " +
				" and l.prepLevelID<=:prepLevelID order by l.operDate,l.operTime, l.logID";
		Query query = getSession().createQuery(hql);
		query.setInteger("datasetTypeID", datasetTypeID);
		query.setInteger("etlProcID", etlProcID);
		query.setInteger("operDate", operDate);
		query.setInteger("operTime", operTime);
		query.setInteger("prepLevelID", prepLevelID);
		return query.list();
	}
	
	@Override
	public int findETLRunLogNum(Integer datasetTypeID, int startOperDate, int startOperTime, int endOperDate, int endOperTime, int prepLevelID) {
		String hql = "select count(l) from ETLRunLog as l where l.prepLevelID<=:prepLevelID" +
				" and (l.operDate >:startOperDate or l.operDate =:startOperDate and l.operTime >=:startOperTime)" +
				" and (l.operDate <:endOperDate or l.operDate =:endOperDate and l.operTime <=:endOperTime)";
		if (datasetTypeID != null) {
			hql +=" and l.datasetTypeID=:datasetTypeID";
		}
		Query query = getSession().createQuery(hql);
		query.setInteger("prepLevelID", prepLevelID);
		query.setInteger("startOperDate", startOperDate);
		query.setInteger("startOperTime", startOperTime);
		query.setInteger("endOperDate", endOperDate);
		query.setInteger("endOperTime", endOperTime);
		if (datasetTypeID != null) {
			query.setInteger("datasetTypeID", datasetTypeID);
		}
		Long num = (Long)query.uniqueResult();
		return num.intValue();
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<ProgExeLog> findPELogsByDatasetTypeId(int datasetTypeID,
			int startOperDate, int startOperTime, int endOperDate, int endOperTime) {
		String hql = "select l from ProgExeLog as l where l.datasetTypeID=:datasetTypeID and l.etlType=:etlType" +
				" and (l.operDate >:startOperDate or l.operDate =:startOperDate and l.operTime >=:startOperTime)" +
				" and (l.operDate <:endOperDate or l.operDate =:endOperDate and l.operTime <=:endOperTime)" +
				" order by l.operDate,l.operTime, l.logID";
		Query query = getSession().createQuery(hql);
		query.setInteger("datasetTypeID", datasetTypeID);
		query.setCharacter("etlType", ProgExeLog.ETL_TYPE_JOB);
		query.setInteger("startOperDate", startOperDate);
		query.setInteger("startOperTime", startOperTime);
		query.setInteger("endOperDate", endOperDate);
		query.setInteger("endOperTime", endOperTime);

		return query.list();
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<ProgExeLog> findPELogsByEtlProcId(int etlProcID,
			int startOperDate, int startOperTime, int endOperDate, int endOperTime) {
		String hql = "select l from ProgExeLog as l where l.etlProcID=:etlProcID and l.etlType=:etlType" +
				" and (l.operDate >:startOperDate or l.operDate =:startOperDate and l.operTime >=:startOperTime)" +
				" and (l.operDate <:endOperDate or l.operDate =:endOperDate and l.operTime <=:endOperTime)" +
				" order by l.operDate,l.operTime, l.logID";
		Query query = getSession().createQuery(hql);
		query.setInteger("etlProcID", etlProcID);
		query.setCharacter("etlType", ProgExeLog.ETL_TYPE_JOB);
		query.setInteger("startOperDate", startOperDate);
		query.setInteger("startOperTime", startOperTime);
		query.setInteger("endOperDate", endOperDate);
		query.setInteger("endOperTime", endOperTime);

		return query.list();
	}
	
	@Override
	public Date findDatabaseDate() {
		Query query = getSession().getNamedQuery("ETLLogDAO.findDatabaseDate");
		Object val = query.uniqueResult();
		return (Date)val;
	}
	
	@SuppressWarnings("deprecation")
	@Override
	public void saveRunLog(int etlProcID, int prepLevelID, char runStatus, String resultMsg) {
		try {
			CallableStatement proc = getSession().connection().prepareCall("{call EB_METADB.P_ETL_RUN_LOG_ADD(?,?,?,?,?,?,?,?)}");
			proc.setInt(1, prepLevelID);
			proc.setInt(2, etlProcID);
			proc.setNull(3, Types.INTEGER);
			proc.setNull(4, Types.VARCHAR);
			proc.setString(5, String.valueOf(runStatus));
			proc.setString(6, resultMsg);
			proc.registerOutParameter(7, Types.VARCHAR);
			proc.registerOutParameter(8, Types.VARCHAR);
			proc.execute();
		    String msg = proc.getString(7);
		    String code = proc.getString(8);
		    if ("-1".equals(code)) {
		    	throw new SQLException(msg);
		    }
		    proc.close();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new HibernateException(e);
		}
		
	}
}
